package databasetest;
//안녕?
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.Vector;

import javax.swing.table.DefaultTableModel;

public class ConectDB {

	public Connection con;
	public ResultSet rs;
	public Statement st;
	public PreparedStatement psmt;
	public CallableStatement csmt;

	public ConectDB() {
		//드라이버 로딩
		try{
			Class.forName("oracle.jdbc.OracleDriver");
			System.out.println("드라이버 로딩 성공");
		}
		catch (ClassNotFoundException e) {
			System.out.println("드라이버 로딩 실패");
		}
		//데이터 베이스 연결
		try {
			con = DriverManager.getConnection(
					//"jdbc:oracle:thin:@192.168.10.114:1521:xe",
					//"hr","hr");
					"jdbc:oracle:thin:@localhost:1521:xe",
					"semi","1234");
			System.out.println("데이타베이스 연결 성공:con="+con);
		} 
		catch(SQLException E){
			System.out.println("데이타베이스 연결 실패");
		}
	}

	//사번별 검색
	public void sabernSearch(String ym, String id,  DefaultTableModel model){
		model.setNumRows(0);
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select t.memberid, irum, t.team, position, t.pay,");
			sql.append("(select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1),");
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0),");
			sql.append("t.pay*((select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1) +"); 
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0)) ");
			sql.append("from memberdata t where memberid = ?");

			psmt = con.prepareStatement(sql.toString());
			psmt.setString(1, id);

			rs=psmt.executeQuery();
			tablePrn(rs,model);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//부서별 검색
	public void teamSearch(String ym, String team,  DefaultTableModel model){
		model.setNumRows(0);
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select t.memberid, irum, team, position, pay,");
			sql.append("(select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1),");
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0),");
			sql.append("t.pay*((select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1) +"); 
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0)) ");
			sql.append("from memberdata t where team=?");
			psmt = con.prepareStatement(sql.toString());
			psmt.setString(1, team);
			rs=psmt.executeQuery();
			tablePrn(rs,model);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//직책별 검색
	public void positionSearch(String ym, String position,  DefaultTableModel model){
		model.setNumRows(0);
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select t.memberid, irum, team, position, pay,");
			sql.append("(select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1),");
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0),");
			sql.append("t.pay*((select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1) +"); 
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0)) ");
			sql.append("from memberdata t where position=?");
			psmt = con.prepareStatement(sql.toString());
			psmt.setString(1, position);
			rs=psmt.executeQuery();
			tablePrn(rs,model);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//날짜별 검색
	public void dateSearch(String ym,DefaultTableModel model){
		model.setNumRows(0);
		try {
			StringBuilder sql = new StringBuilder();
			sql.append("select t.memberid, irum, team, position, pay,");
			sql.append("(select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1),");
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0),");
			sql.append("t.pay*((select count(*) from chuldata t2 where t.memberid = t2.memberid and chuldate like '"+ym+"%' and chultype = 1) +"); 
			sql.append("(select count(*) from holydata t3 where t.memberid = t3.memberid and holydate like '"+ym+"%' and holytype != 0)) ");
			sql.append("from memberdata t");
			psmt = con.prepareStatement(sql.toString());
			rs=psmt.executeQuery();
			tablePrn(rs,model);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void holyTable(String id, String ym,DefaultTableModel model){
		StringBuilder sql = new StringBuilder();
		try {
			sql.append("select memberid ,");
			sql.append("(select count(*) from holydata h2 where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=1),");
			sql.append("(select count(*) from holydata h2 where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=2),");
			sql.append("(select count(*) from holydata h2 where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=3),");
			sql.append("(select count(*) from holydata h2 where h1.memberid=h2.memberid and HOLYDATE like '"+ym+"%' and HOLYTYPE=4) ");
			sql.append("from holydata h1 group by memberid having memberid = ?");
			psmt = con.prepareStatement(sql.toString());
			psmt.setString(1, id);
			rs = psmt.executeQuery();
			rs.next();
			Vector<String> vec = new Vector<String>();
			vec.add(rs.getString(1));
			vec.add(rs.getInt(2)+"일");
			vec.add(rs.getInt(3)+"일");
			vec.add(rs.getInt(4)+"일");
			vec.add(rs.getInt(5)+"일");
			model.addRow(vec);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//메서드들의 결과 값 모델에 저장
	public void tablePrn(ResultSet rs,DefaultTableModel model){
		try {
			while(rs.next()){
				Vector<String> vec = new Vector<String>();
				DecimalFormat df = new DecimalFormat("#,###");//콤마표시를 위해
				vec.add(rs.getString(1));//사번
				vec.add(rs.getString(3));//부서
				vec.add(rs.getString(4));//직책
				vec.add(rs.getString(2)); //성명
				vec.add(df.format(rs.getInt(5))+"원"); //일당
				vec.add(Integer.toString(rs.getInt(6))+"일");//출근일수     
				vec.add(Integer.toString(rs.getInt(7))+"일");//유급일수
				vec.add(df.format(rs.getInt(8))+"원");//일당 * 출근일수
				if(rs.getInt(6)!=0 || rs.getInt(7) !=0){
					model.addRow(vec);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//사원 수 확인 메서드
	public int memberCount(){
		int mc=0;
		try {
			String sql = "select count(*) from memberdata";
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
			rs.next();
			mc = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mc;
	}

	//사번 유무 체크
	public int memCheck(String id){
		int check=0;
		try {
			String sql="select count(*) from memberdata where memberid=?";
			psmt = con.prepareStatement(sql);
			psmt.setString(1, id);
			rs = psmt.executeQuery();
			rs.next();
			check = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return check;
	}

	//salary 테이블에 아이디+년 체크 메서드
	public int smCheck(int i){
		int check=0;
		try {
			String sql = "select count(*) from salary where memberid='A' || "+i+" and s_year=2014";
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
			rs.next();
			check = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return check;
	}

	//급여 테이블 입력
	public void salary(){
		try {
			int end = memberCount();
			StringBuilder sql = new StringBuilder();
			sql.append("BEGIN ");
			sql.append("FOR i IN 1.."+end+" LOOP ");
			sql.append("insert into salary(S_no,memberid,s_year,s_4) values(seq_s_no.nextval,'A' || i,2014,");
			sql.append("(select pay*((select count(*) from chuldata  where memberid='A' || i and chuldate like '%14/04%' and chultype=1 group by memberid) +");
			sql.append("(select count(*) from holydata where memberid='A' || i and holydate like '%14/04%' and holytype!=0 group by memberid)) ");
			sql.append("from memberdata where memberid = 'A' || i))");

			sql.append("update salary set s_5 = (select pay * (");
			sql.append("(select count(*) from chuldata  where memberid='A' || i and chuldate like '%14/05%' and chultype=1 group by memberid) +");
			sql.append("(select count(*) from holydata where memberid='A' || i and holydate like '%14/05%' and holytype!=0 group by memberid))");
			sql.append("from memberdata where memberid = 'A' || i) where memberid = 'A' || i and s_year=2014;");

			sql.append("END LOOP");
			sql.append("END");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//자원 반납
	public void dbClose(){
		try {
			if(rs != null) rs.close();
			if(st != null) st.close();
			if(psmt != null) psmt.close();
			if(csmt != null) csmt.close();
			if(con != null) con.close();
		} catch (SQLException e) {}
	}

}
